﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON

GO
CREATE TABLE dbo.bht_NemoTableTimes
	(
	GroupId int NOT NULL,
	LastTime datetime NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.bht_NemoTableTimes ADD CONSTRAINT
	PK_bht_NemoTableTimes PRIMARY KEY CLUSTERED 
	(
	GroupId
	) ON [PRIMARY]

	
GO
CREATE TABLE dbo.bht_NemoTableTimes_TempWork
	(
	GroupId int NOT NULL,
	LastTime datetime NULL
	)  ON [PRIMARY]
GO



CREATE PROCEDURE [dbo].[bhp_NemoTablesLastModificationTimeGet]
AS
BEGIN
DECLARE 
		@TabId		INT,
		@RelTabId	INT,
		@Type		VARCHAR(10),
		@Sql		VARCHAR(8000)
		
	SET XACT_ABORT ON
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	BEGIN TRANSACTION

	DELETE FROM bht_NemoTableTimes_TempWork
	
	DECLARE ntables CURSOR LOCAL FAST_FORWARD FOR
		SELECT 
			t.typ, 
			CASE t.typ 
				WHEN 't' THEN t.id
				ELSE r.tab END as tab
			, ISNULL(r.rel, 0)
		FROM 
			sTree t
			LEFT JOIN sRel r ON t.id = r.field AND rel > 0
		WHERE 
			typ = 'fn' OR typ = 't' 
			

	
	OPEN ntables
	
	WHILE 1 = 1
	BEGIN
	
		FETCH NEXT FROM ntables INTO @Type, @TabId, @RelTabId
		IF(@@FETCH_STATUS <> 0) BREAK;
		
		IF @Type = 't'
		
		SET @Sql = 
			'INSERT INTO bht_NemoTableTimes_TempWork
			SELECT 
				' + CAST(@TabId AS VARCHAR(10)) + ' as TabId, MAX(mod_time) as LastModTime
			FROM
				(SELECT MAX(ctime) as mod_time FROM T' + CAST(@TabId AS VARCHAR(10)) + ' WHERE ctime IS NOT NULL
				UNION
				SELECT MAX(mtime) FROM T' + CAST(@TabId AS VARCHAR(10)) + ' WHERE mtime IS NOT NULL) x
			WHERE x.mod_time IS NOT NULL'
		
		ELSE
		
		--SET @Sql = 
		--	'INSERT INTO bht_NemoTableTimes_TempWork
		--	SELECT
		--		' + CAST(@TabId AS VARCHAR(10)) + ', MAX(mod_time)
		--	FROM(
		--		SELECT
		--			MAX(ctime) as mod_time
		--		FROM
		--			T' + CAST(@TabId AS VARCHAR(10)) + '_T' + CAST(@RelTabId AS VARCHAR(10)) + '  WHERE ctime IS NOT NULL
		--		UNION
		--		SELECT
		--			MAX(ctime)
		--		FROM
		--			T' + CAST(@TabId AS VARCHAR(10)) + '_T' + CAST(@RelTabId AS VARCHAR(10)) + ' WHERE mtime IS NOT NULL) x
		--	WHERE x.mod_time IS NOT NULL'
		
		--PRINT @Sql
		EXEC(@Sql)
		
	END
	
	CLOSE ntables

	INSERT INTO bht_NemoTableTimes_TempWork
	SELECT 
		0, MAX([time])
	FROM
		(SELECT
			ctime as [time]
		FROM
			sTree
		WHERE
			ctime IS NOT NULL
		UNION
		SELECT
			mtime
		FROM
			sTree
		WHERE
			mtime IS NOT NULL) x

	DELETE FROM bht_NemoTableTimes

	INSERT INTO bht_NemoTableTimes
	SELECT 
		GroupId, MAX(LastTime)
	FROM 
		bht_NemoTableTimes_TempWork
	WHERE
		LastTime IS NOT NULL
	GROUP BY 
		GroupId
	
	COMMIT TRANSACTION
	
	SELECT * FROM bht_NemoTableTimes
END
